﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Xml;
using System.Xml.Serialization;
using SubSonic;
using SubSonic.Utilities;
using Commerce.Common;

// namespace Commerce.Common
namespace CommerceForUmbraco.usercontrols
{
    public partial class ProductCollection : ActiveList<Product, ProductCollection> 
    {

        /// <summary>
        /// This is a ProWorks extension of the product search!
        /// </summary>
        public static Commerce.Common.ProductCollection GetProductCollectionFromSearchInput(string searchInput, out string generatedSQL)
        {
            generatedSQL = "";
            
            if ( string.IsNullOrEmpty(searchInput) )
            {
                generatedSQL = "NO SQL GENERATED - search input was NULL or empty!";

                // return an empty but no-null colleciton
                return new Commerce.Common.ProductCollection();
            }

            try
            {
                string sql = "SELECT * FROM  CSK_Store_Product WHERE isDeleted = 0 AND statusID < 99 AND ";
                string[] searchFields = { "productName", "shortDescription" };

                string[] searchTokens;
                Dictionary<string, string> searchParamValueList;
                sql += String.Format("({0})", BuildWhereClauseFromRawInput(searchInput, searchFields, out searchTokens, out searchParamValueList) );

                // Might be used to debug!
                generatedSQL = sql;

                SubSonic.QueryCommand cmd = new SubSonic.QueryCommand(sql, Product.Schema.Provider.Name);

                // Add all the params to the command...
                foreach( string paramName in searchParamValueList.Keys )
                {
                    string paramValue = searchParamValueList[paramName];
                    cmd.AddParameter(paramName, paramValue, DbType.String);
                }

                IDataReader rdr = SubSonic.DataService.GetReader(cmd);
                Commerce.Common.ProductCollection coll = new Commerce.Common.ProductCollection();
                coll.LoadAndCloseReader(rdr);

                return coll;
            }
            catch (Exception x)
            {
                generatedSQL = String.Format ( "SEARCH ERROR - an exception was thrown while constructing the SQL and/or running it.  Generated SQL: {0}  Details:  {1}", generatedSQL, x.Message );
                umbraco.BusinessLogic.Log.Add(umbraco.BusinessLogic.LogTypes.Error, umbraco.BusinessLogic.User.GetUser(0), -1, generatedSQL);
            }

            // should have returned above - must have failed:
            // return an empty but no-null colleciton
            return new Commerce.Common.ProductCollection();

        }

        private static string BuildWhereClauseFromRawInput(string searchInput, string[] searchFields, out string[] searchTokens, out Dictionary<string, string> searchParamValueList)
        {
            searchTokens = null;
            searchParamValueList = new Dictionary<string, string>();

            char[] searchTokenDelimiters = { ' ', '+', '-', '"', ',' };

            searchTokens = searchInput.Split(searchTokenDelimiters, StringSplitOptions.RemoveEmptyEntries);


            // First pass, build the searchParamValueList for the caller
            for (int i = 0; i < searchFields.Length; i++)
            {
                for (int j = 0; j < searchTokens.Length; j++)
                {
                    string paramName = string.Format("@{0}{1}", searchFields[i], j + 1);
                    string paramValue = searchTokens[j];

                    searchParamValueList.Add(paramName, paramValue);
                }
            }

            //searchParams = searchParamsList.ToArray();

            // Second pass: build the clause:
            // Example:
            //((sku LIKE N'%Toes%') AND (sku LIKE N'%Natural%'))
            // OR
            //((productName LIKE N'%Toes%') AND (productName LIKE N'%Natural%')) 
            // OR
            //((shortDescription LIKE N'%Toes%') AND (shortDescription LIKE N'%Natural%')) 
         

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < searchFields.Length; i++)
            {
                sb.Append("(");
                for (int j = 0; j < searchTokens.Length; j++)
                {
                    sb.Append("(");
                    
                    string paramName = string.Format("@{0}{1}", searchFields[i], j + 1);

                    sb.Append(string.Format ( "{0} LIKE '%' + {1} + '%'", searchFields[i], paramName));

                    sb.Append(")");

                    if (j < searchTokens.Length-1)
                    {
                        sb.Append(" AND ");
                    }
                }

                sb.Append(")");

                if (i < searchFields.Length - 1)
                {
                    sb.Append(" OR ");
                }
            }

            return sb.ToString();
        }
    }
}
